﻿using System;
using System.Data;
using System.Data.SqlClient;


namespace LenderClearingHouse
{
  /// <summary>
  /// Data Access Class for the web application
  /// </summary>
  public class DataAccess
  {
    protected SqlConnection myConnection;

    public DataAccess()
    {
      //Retrieves database connection string
      myConnection = Utilities.GetConnection();
    }

    //Executes stored procedure
    public int ExecuteProcedure(string strdProc)
    {
      int rows = 0; //number of rows modified

      //Set stored procedure name and command type
      SqlCommand myCommand = new SqlCommand(strdProc, myConnection);
      myCommand.CommandType = CommandType.StoredProcedure;

      try
      {
        myConnection.Open();
        rows = myCommand.ExecuteNonQuery();
      }
      catch (Exception ex)
      {
        //Utilities.LogError(ex);
        throw ex;
      }
      finally
      {
        myConnection.Close();
      }
      return rows;
    }

    public int ExecuteProcedure(string strdProc, string[] parameters, object[] values)
    {
      int rows = 0; //number of rows modified

      //Set stored procedure name and command type
      SqlCommand myCommand = new SqlCommand(strdProc, myConnection);
      myCommand.CommandType = CommandType.StoredProcedure;

      if (parameters != null)
      {
        for (int i = 0; i < parameters.Length; i++)
        {
          myCommand.Parameters.AddWithValue(parameters[i], values[i]);
        }
      }

      try
      {
        myConnection.Open();
        rows = myCommand.ExecuteNonQuery();
      }
      finally
      {
        myConnection.Close();
      }
      return rows;
    }//End Execute Procedure

    //Execute and return Datatable by sql string
    public DataTable ExecuteSQL(string sql)
    {
      DataTable da = new DataTable();
      //Set stored procedure name and command type
      SqlCommand myCommand = new SqlCommand(sql, myConnection);
      myCommand.CommandType = CommandType.Text;

      try
      {
        SqlDataAdapter ad = new SqlDataAdapter(myCommand);
        ad.Fill(da);
      }
      catch (Exception ex)
      {
        //Utilities.LogError(ex);
        throw ex;
      }
      return da;
    }// End ExecuteSQL

    //Executes stored procedure
    public int ExecuteSQLRowCount(string sql)
    {
      int rows = 0; //number of rows modified

      //Set stored procedure name and command type
      SqlCommand myCommand = new SqlCommand(sql, myConnection);
      myCommand.CommandType = CommandType.Text;

      try
      {
        myConnection.Open();
        rows = (int)myCommand.ExecuteScalar();
      }
      catch (Exception ex)
      {
        //Utilities.LogError(ex);
        throw ex;
      }
      finally
      {
        myConnection.Close();
      }
      return rows;
    }

    //Executes stored procedure
    public int ExecuteSQLNonQuery(string sql)
    {
      int rows = 0; //number of rows modified

      //Set stored procedure name and command type
      SqlCommand myCommand = new SqlCommand(sql, myConnection);
      myCommand.CommandType = CommandType.Text;

      try
      {
        myConnection.Open();
        rows = (int)myCommand.ExecuteNonQuery();
      }
      catch (Exception ex)
      {
        //Utilities.LogError(ex);
        throw ex;
      }
      finally
      {
        myConnection.Close();
      }
      return rows;
    }

    //Returns DataTable
    public DataTable GetDataTable(string strdProc, string value1)
    {
      DataTable table = new DataTable();

      //Set stored procedure name and command type
      SqlCommand myCommand = new SqlCommand(strdProc, myConnection);
      myCommand.CommandType = CommandType.StoredProcedure;

      //Set input and output parameters for stored procedure
      SqlParameter Column1 = new SqlParameter("Value1", SqlDbType.VarChar);
      Column1.Direction = ParameterDirection.Input;
      Column1.Value = value1;
      myCommand.Parameters.Add(Column1);

      try
      {
        SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
        adapter.Fill(table);
      }
      catch (Exception ex)
      {
        //Utilities.LogError(ex);
        throw ex;
      }
      return table;
    }//End GetDataTable

    //Returns DataTable
    //public DataTable GetDataTable(string strdProc, string value1, string value2, string value3)
    //{
    //  DataTable table = new DataTable();

    //  //Set stored procedure name and command type
    //  SqlCommand myCommand = new SqlCommand(strdProc, myConnection);
    //  myCommand.CommandType = CommandType.StoredProcedure;

    //  //Set input and output parameters for stored procedure
    //  SqlParameter Column1 = new SqlParameter("Value1", SqlDbType.VarChar);
    //  Column1.Direction = ParameterDirection.Input;
    //  Column1.Value = value1;
    //  myCommand.Parameters.Add(Column1);

    //  //Set input and output parameters for stored procedure
    //  SqlParameter Column2 = new SqlParameter("Value2", SqlDbType.VarChar);
    //  Column2.Direction = ParameterDirection.Input;
    //  Column2.Value = value2;
    //  myCommand.Parameters.Add(Column2);

    //  //Set input and output parameters for stored procedure
    //  SqlParameter Column3 = new SqlParameter("Value3", SqlDbType.VarChar);
    //  Column3.Direction = ParameterDirection.Input;
    //  Column3.Value = value3;
    //  myCommand.Parameters.Add(Column3);

    //  try
    //  {
    //    SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
    //    adapter.Fill(table);
    //  }
    //  catch (Exception ex)
    //  {
    //    Utilities.LogError(ex);
    //    throw ex;
    //  }
    //  return table;
    //}//End GetDataTable

    public DataTable GetDataTable(string strdProc, string[] parameters, object[] values)
    {
      DataTable table = new DataTable();

      //Set stored procedure name and command type
      SqlCommand myCommand = new SqlCommand(strdProc, myConnection);
      myCommand.CommandType = CommandType.StoredProcedure;

      if (parameters != null)
      {
        for (int i = 0; i < parameters.Length; i++)
        {
          myCommand.Parameters.AddWithValue(parameters[i], values[i]);
        }
      }

      try
      {
        SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
        adapter.Fill(table);
      }
      catch (Exception ex)
      {
        //Utilities.LogError(ex);
        throw ex;
      }
      return table;
    }//End GetDataTable

    public DataTable GetDataTable(string strdProc)
    {
      // Create Instance of Connection and Command Object
      SqlDataAdapter myCommand = new SqlDataAdapter(strdProc, myConnection);

      //Set command type to stored procedure
      myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

      // Create and Fill the DataSet
      DataTable myDataTable = new DataTable();
      myCommand.Fill(myDataTable);

      // Return the DataSet
      return myDataTable;

    }//End GetDataTable()

    //Unused funtion to select cahced table
   /* public DataTable GetCacheTable(string strdProc)
    {
      string connectionString = ApplicationConfiguration.DbConnectionString;

      DataTable dt = new DataTable();

      using (SqlConnection connection = new SqlConnection(connectionString))
      {
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = strdProc;

        System.Web.Caching.SqlCacheDependency new_dependency = new System.Web.Caching.SqlCacheDependency(command);

        SqlDataAdapter DA1 = new SqlDataAdapter();
        DA1.SelectCommand = command;

        DataSet DS1 = new DataSet();

        DA1.Fill(DS1);

        dt = DS1.Tables[0];

        HttpContext.Current.Cache.Insert("DataTable", dt, new_dependency);
      }

      return dt;
    }*/

  }//End DataAccess
}